﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;


namespace QuanLyBanHang.DAL
{
    public class SanPham_DAL
    {
        dbConnection conn;
        public SanPham_DAL()
        {
            conn = new dbConnection();
        }
        public DataTable SelectSP(string key)
        {
            string query = "select * from SANPHAM where TenSanPham like '%'+@tensp+'%'";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@tensp", key);
            return conn.execSelectQuery(query, p);
        }
        public bool insertSP(string tensp, int soluong, int gia, string loai, string mota)
        {
            string query = "insert into SANPHAM values(@tensp,@soluong,@gia,@loai,@mota)";
            SqlParameter[] p = new SqlParameter[5];
            p[0] = new SqlParameter("@tensp", tensp);
            p[1] = new SqlParameter("@soluong", soluong);
            p[2] = new SqlParameter("@gia", gia);
            p[3] = new SqlParameter("@loai",loai);
            p[4] = new SqlParameter("@mota", mota);
            return conn.execInsertQuery(query, p);
        }
        public bool updateSP(int masp, string tensp, int soluong, int gia, string loai, string mota)
        {
            string query = "update SANPHAM set TenSanPham=@tensp,SoLuong=@soluong,Gia=@gia,LoaiSanPham=@loai,MoTa=@mota where MaSanPham=@masp";
            SqlParameter[] p = new SqlParameter[6];            
            p[0] = new SqlParameter("@tensp", tensp);
            p[1] = new SqlParameter("@soluong", soluong);
            p[2] = new SqlParameter("@gia", gia);
            p[3] = new SqlParameter("@loai", loai);
            p[4] = new SqlParameter("@mota", mota);
            p[5] = new SqlParameter("@masp",masp);
            return conn.execUpdateQuery(query,p);
        }
        public bool deleteSP(int masp)
        {
            string query = "delete from SANPHAM where MaSanPham =@masp";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@masp", masp);
            return conn.execDeleteQuery(query, p);
        }
        public string getNameByID(int id)
        {
            try
            {
                string query = "select * from SANPHAM where MaSanPham=@masp";
                SqlParameter[] p = new SqlParameter[1];
                p[0] = new SqlParameter("@masp", id);
                DataTable dt = new DataTable();
                dt = conn.execSelectQuery(query, p);
                if (dt != null)
                {
                    return dt.Rows[0][1].ToString();
                }
                else return null;
            }
            catch(Exception exc)
            {
                
                return null;
            }
        }
        public int getPrice(int id)
        {
            try
            {
                string query = "select * from SANPHAM where MaSanPham=@masp";
                SqlParameter[] p = new SqlParameter[1];
                p[0] = new SqlParameter("@masp", id);
                DataTable dt = new DataTable();
                dt = conn.execSelectQuery(query, p);
                if (dt != null)
                {
                    return int.Parse(dt.Rows[0][3].ToString());
                }
                else return 0;
            }
            catch(Exception exc)
            {
                MessageBox.Show("Không có sản phẩm này");
                return 0;
            }

        }
        public DataTable NhapXuat(int thang, int nam)
        {
            string query = "NhapXuat";
            SqlParameter[] p = new SqlParameter[2];
            p[0] = new SqlParameter("@thang", thang);
            p[1] = new SqlParameter("@nam", nam);
            return conn.execSelectProcedureQuery(query, p);
        }
        
    }
}
